﻿using com.nm;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library.Helper;
using NHibernateVertica;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using george.Data;
using Vertica.Data.VerticaClient;
using VerticaAPI.model;
using com.nm.library;

namespace VerticaAPI
{
    [Description("配件中包装")]
    public class SKCSizeTypeHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region GetList 获取配件中包装列表

        [MustLogin]
        [Description("获取配件中包装列表")]
        [ReturnType(typeof(PaperData<SKCSizeTypeModel.ListModel>))]
        public ApiResult GetList(
            [ParamDescription("系数")] int SizeLimit = 0,
            [ParamDescription("货号")] string MaterialID = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 100)
        {
            try
            {
                var sqlStr = @"select {0} from ( 
select b.KindName,a.MaterialID,a.SizeLimit
,row_number()over(order by b.KindName,a.MaterialID,a.SizeLimit) as rowIndex
from ndw.ndw_SKCSizeType a
inner join nods.nods_erp_SD_Mat_Material2 b on a.MaterialID=b.MaterialID 
  and a.ExtrSource = b.ExtrSource and a.CompanyID=b.CompanyID
where 1=1 {1} {2}
order by b.KindName,a.MaterialID,a.SizeLimit
) table1 
";
                string whereStr = "";
                if (SizeLimit > 0)
                {
                    whereStr += " and a.SizeLimit is null ";
                }
                if (!string.IsNullOrWhiteSpace(MaterialID))
                {
                    whereStr += $" and a.MaterialID like '%{MaterialID}%' ";
                }

                var countSql = string.Format(sqlStr, "count(1)", $" and a.ExtrSource='{UserInfo.UserExtrSource}' and a.CompanyID='{UserInfo.UserCompanyId}'", whereStr);
                var count = Convert.ToInt32(Helper.ExecuteScalar(new VerticaCommand(countSql, null)));

                var result = PaperData<SKCSizeTypeModel.ListModel>.Create(count, Size, Page);
                var listSql = string.Format(sqlStr, "*", $" and a.ExtrSource='{UserInfo.UserExtrSource}' and a.CompanyID='{UserInfo.UserCompanyId}'",whereStr);
                if (Page != 0 && Size != 0)
                {
                    listSql += string.Format($@" where rowIndex BETWEEN ({Page}-1)*{Size}+1 AND {Page}*{Size} ");
                }

                var dt = Helper.ExecuteDataTable(new VerticaCommand(listSql, null));
                result.Items = ModelConvertHelper<SKCSizeTypeModel.ListModel>.ConvertToModel(dt);
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region UpdateList 修改配件中包装数据

        [MustLogin]
        [Description("修改配件中包装数据")]
        [JsonType]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult UpdateList(SKCSizeTypeModel.UpdateModelList list)
        {
            #region 验证数据

            if (list?.List == null)
            {
                return Error("接收参数错误，请刷新重试");
            }

            //至少一条数据
            if (list.List.Count < 1)
            {
                return Error("请先勾选要保存的数据");
            }

            #endregion

            var updateCmd = new List<CommandInfo>();
            foreach (var item in list.List)
            {
                var sql = $" update ndw.ndw_SKCSizeType set SizeLimit = {item.SizeLimit} where  ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}'  and MaterialID = '{item.MaterialID}' ";
                updateCmd.Add(new CommandInfo(sql, null));
            }

            int n = Helper.ExecuteSqlTran(updateCmd);
            if (n <= 0)
            {
                return Error("保存失败，请重试");
            }
            return Success(0);
        }

        #endregion

        #region

        /// <summary>
        /// 删除临时表
        /// </summary>
        /// <param name="TempTableName"></param>
        /// Writer：ljj
        /// Create Date：2017-11-16
        public void DropTempTable(string TempTableName)
        {
            try
            {
                Helper.DropTempTable(TempTableName);
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }

        /// <summary>
        /// 创建临时表
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="TempTableName"></param>
        /// Writer：ljj
        /// Create Date：2017-11-16
        public void CreateTempTable(DataTable dt, string TempTableName)
        {
            try
            {
                var sql = $@"CREATE TABLE {TempTableName}
(
    CompanyID varchar(30),
    ExtrSource varchar(30),
    MaterialID varchar(30),
    KindName varchar(30),
    SizeLimit int
)";
                var extrSource = UserInfo.UserExtrSource;
                var companyID = UserInfo.UserCompanyId;
                if (!dt.Columns.Contains("ExtrSource"))
                {
                    dt.Columns.Add("ExtrSource", System.Type.GetType("System.String"));
                    foreach (DataRow item in dt.Rows)
                    {
                        item["ExtrSource"] = extrSource;
                    }
                }
                if (!dt.Columns.Contains("CompanyID"))
                {
                    dt.Columns.Add("CompanyID", System.Type.GetType("System.String"));
                    foreach (DataRow item in dt.Rows)
                    {
                        item["CompanyID"] = companyID;
                    }
                }
                Helper.ExecuteSqlTran(dt, TempTableName, sql);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="TempTableName"></param>
        /// <returns></returns>
        /// Writer：ljj
        /// Create Date：2017-11-16
        public DataTable ImportSKCSizeType(string TempTableName)
        {
            var uid = Guid.NewGuid().ToString("N");
            var finalTempTableName = $" ndw.ndw_{uid} ";
            try
            {
                var extrSource = UserInfo.UserExtrSource;
                var companyID = UserInfo.UserCompanyId;
                var sql = $@"SELECT  table1.ExtrSource ,
        table1.CompanyID ,
        table1.MaterialID ,
        table1.SizeLimit ,
        table1.KindName ,
        s.SizeTypeCode
FROM    {TempTableName} table1
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_SKCSizeType
                    WHERE   ExtrSource = '{extrSource}'
                            AND CompanyID = '{companyID}'
                  ) s ON s.ExtrSource = table1.ExtrSource
                         AND s.CompanyID = table1.CompanyID
                         AND s.MaterialID = table1.MaterialID";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<SKCSizeTypeModel.FinalModel>.ConvertToModel(dt);
                var resultList = list.Where(d => d.ExtrSource == extrSource && d.CompanyID == companyID &&
                                                 d.SizeTypeCode != null).Select(d => d).ToList();
                var resultDT = DataTableHelper.LinqToDataTable(resultList);
                if (resultDT.Columns.Contains("KindName"))
                {
                    resultDT.Columns.Remove("KindName");
                }
                MergeSKCSizeType(resultDT, finalTempTableName);
                var errorList = list.Except(resultList);
                var errorDT = DataTableHelper.LinqToDataTable(errorList);
                return errorDT;
            }
            catch (Exception ex)
            {
                throw(ex);
            }
            finally
            {
                DropTempTable(finalTempTableName);
            }
        }

        /// <summary>
        /// Merge
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="TempTableName"></param>
        /// Writer：ljj
        /// Create Date：2017-11-16
        public void MergeSKCSizeType(DataTable dt, string TempTableName)
        {
            try
            {
                const string onConditions = @"targetTable.CompanyID=sourceTable.CompanyID
    and targetTable.ExtrSource=sourceTable.ExtrSource
	and targetTable.MaterialID=sourceTable.MaterialID";
                const string setConditions = @"SizeLimit=sourceTable.SizeLimit";
                const string insertConditions = "";
                const string valuesConditions = "";
                Helper.Merge(TempTableName, "ndw.ndw_SKCSizeType", dt, onConditions, setConditions,
                    insertConditions,
                    valuesConditions, false);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion
    }
}
